﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using KPIS.GERP.Models.ICM;
using KPIS.GERP.Models.SYS.ViewModel;
using KPIS.GERP.Models.Class;
using KPIS.GERP.Models.SYS;
using KPIS.GERP.Models;
using KPIS.DBM;
using KPIS.GERP.Models.BGM;

namespace KPIS.GERP.WCF.SYS
{
    public partial class SysApprovalService
    {
        public void OnWorkflowStartupInit(CreateTaskVM model)
        {
            try
            {

            }
            catch (Exception ex)
            {
                GerpLog.Error(ex.Message, ex);
            }
        }

        public void OnWorkflowStartupLoad(CreateTaskVM model)
        {
            try
            {

            }
            catch (Exception ex)
            {
                GerpLog.Error(ex.Message, ex);
            }
        }

        public void OnWorkflowStartupCompleted(CreateTaskVM model)
        {
            try
            {

            }
            catch (Exception ex)
            {
                GerpLog.Error(ex.Message, ex);
            }
        }

        public void OnWorkflowEscalateStart(ref ActionTaskVM model)
        {
            try
            {
                using (GERPContext db = new GERPContext(true))
                {
                    if (!model.TaskId.HasValue && model.TaskHistoryId.HasValue)
                    {
                        var taskHistory = db.TaskHistories.Find(model.TaskHistoryId.Value);
                        model.TaskId = taskHistory.TaskId;
                    }
                    int? TaskId = model.TaskId;
                    var task = db.Tasks.Include("ApproveProcess").FirstOrDefault(o => o.TaskId == TaskId);

                    if (model.CommandAction == CommandAction.Approve)
                    {
                        if (task.ApproveProcess.AppPrcGrpId == 6)
                        {
                            var sumAmounts = (from ppri in db.PrePurchaseRequestItems
                                              join ope in db.ProjectOperationExpenses on ppri.ProjOperExpId equals ope.ProjOperExpId
                                              where ppri.PprId == task.RefId
                                              group ppri by ope.ProjOperId into g
                                              select new { ProjOperId = g.Key, Amount = g.Sum(o => o.Amount) }
                                             ).ToList();

                            foreach (var sumAmount in sumAmounts)
                            {
                                var sumReport = (from ope in db.ProjectOperationExpenses
                                                 join rpt in db.ProjectOperationReports on ope.ProjOperExpId equals rpt.ProjOperExpId
                                                 where ope.ProjOperId == sumAmount.ProjOperId
                                                     && rpt.RecordStatus != RecordStatus.DeletedStatus
                                                 select rpt)
                                                 .Sum(o => o.CalAmount);

                                if (sumReport < sumAmount.Amount)
                                {
                                    model.CommandActionErrorResult = "จำนวนเงินในขออนุมัติดำเนินงานไม่พอ ไม่สามารถอนุมัติรายการได้!!";
                                }
                            }
                        }
                        else if (task.ApproveProcess.AppPrcGrpId == 8 || task.ApproveProcess.AppPrcGrpId == 9)
                        {
                            var sumAmounts = (from pri in db.PurchaseRequestItems
                                              join ppri in db.PrePurchaseRequestItems on pri.PrItemId equals ppri.PrItemId
                                              join ope in db.ProjectOperationExpenses on ppri.ProjOperExpId equals ope.ProjOperExpId
                                              join quo in db.BidderQuotations on pri.PrItemId equals quo.PrItemId
                                              where pri.PrId == task.RefId
                                              group pri by ope.ProjOperId into g
                                              select new { ProjOperId = g.Key, Amount = g.Sum(o => o.Amount) }
                                             ).ToList();

                            var except = from pri in db.PurchaseRequestItems
                                         join ppri in db.PrePurchaseRequestItems on pri.PrItemId equals ppri.PrItemId
                                         join rpt in db.ProjectOperationReports on ppri.PprItemId equals rpt.PprItemId
                                         where pri.PrId == task.RefId
                                         select rpt;

                            foreach (var sumAmount in sumAmounts)
                            {
                                var sumReport = (from ope in db.ProjectOperationExpenses
                                                 join rpt in db.ProjectOperationReports on ope.ProjOperExpId equals rpt.ProjOperExpId
                                                 where ope.ProjOperId == sumAmount.ProjOperId
                                                     && rpt.RecordStatus != RecordStatus.DeletedStatus
                                                 select rpt)
                                                 .Except(except)
                                                 .Sum(o => o.CalAmount);

                                if (sumReport < sumAmount.Amount)
                                {
                                    model.CommandActionErrorResult = "จำนวนเงินในขออนุมัติดำเนินงานไม่พอ ไม่สามารถอนุมัติรายการได้!!";
                                }
                            }
                        }
                        else if (task.ApproveProcess.AppPrcGrpId == 10 || task.ApproveProcess.AppPrcGrpId == 11 || task.ApproveProcess.AppPrcGrpId == 12)
                        {
                            var sumAmounts = (from poi in db.PurchaseOrderItems
                                              join bq in db.BidderQuotations on poi.BidderQuoId equals bq.BidderQuoId
                                              join pri in db.PurchaseRequestItems on bq.PrItemId equals pri.PrItemId
                                              join ppri in db.PrePurchaseRequestItems on pri.PrItemId equals ppri.PrItemId
                                              join ope in db.ProjectOperationExpenses on ppri.ProjOperExpId equals ope.ProjOperExpId
                                              where poi.PoId == task.RefId
                                              group bq by ope.ProjOperId into g
                                              select new { ProjOperId = g.Key, Amount = g.Sum(o => o.NetAmount) }
                                             ).ToList();

                            var except = from poi in db.PurchaseOrderItems
                                         join bq in db.BidderQuotations on poi.BidderQuoId equals bq.BidderQuoId
                                         join pri in db.PurchaseRequestItems on bq.PrItemId equals pri.PrItemId
                                         join ppri in db.PrePurchaseRequestItems on pri.PrItemId equals ppri.PrItemId
                                         join rpt in db.ProjectOperationReports on ppri.PprItemId equals rpt.PprItemId
                                         where poi.PoId == task.RefId
                                         select rpt;

                            foreach (var sumAmount in sumAmounts)
                            {
                                var sumReport = (from ope in db.ProjectOperationExpenses
                                                 join rpt in db.ProjectOperationReports on ope.ProjOperExpId equals rpt.ProjOperExpId
                                                 where ope.ProjOperId == sumAmount.ProjOperId
                                                     && rpt.RecordStatus != RecordStatus.DeletedStatus
                                                 select rpt)
                                                 .Except(except)
                                                 .Sum(o => o.CalAmount);

                                if (sumReport < sumAmount.Amount)
                                {
                                    model.CommandActionErrorResult = "จำนวนเงินในขออนุมัติดำเนินงานไม่พอ ไม่สามารถอนุมัติรายการได้!!";
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                model.CommandActionErrorResult = ex.Message;
                GerpLog.Error(ex.Message, ex);
            }
        }

        public void OnWorkflowEscalateComplete(ActionTaskVM model)
        {
            try
            {

            }
            catch (Exception ex)
            {
                GerpLog.Error(ex.Message, ex);
            }
        }

        public void OnWorkflowComplete(ActionTaskVM model)
        {
            using (GERPContext db = new GERPContext(true))
            {
                // Resolve Position Null
                if (model.ByEmployee.Position == null)
                {
                    model.ByEmployee.Position = new GERPContext(false).Positions.FirstOrDefault(o => o.PosId == model.ByEmployee.PosId);
                }

                var task = db.Tasks.Find(model.TaskId);
                string updateReferenceTableRecordStatus;
                switch (model.CommandAction)
                {
                    case CommandAction.Approve:
                        updateReferenceTableRecordStatus = "A";
                        task.ApprovedBy = model.ByEmpId;
                        task.ApprovedWhen = DateTime.Now;
                        task.ApprovedByPosName = model.ByEmployee.Position.PosNo + " - " + model.ByEmployee.Position.Name;
                        task.ApprovedByFullName = model.ByEmployee.FullName;
                        break;
                    case CommandAction.Cancel:
                        updateReferenceTableRecordStatus = "C";
                        task.CanceledBy = model.ByEmpId;
                        task.CanceledWhen = DateTime.Now;
                        task.CanceledByPosName = model.ByEmployee.Position.PosNo + " - " + model.ByEmployee.Position.Name;
                        task.CanceledByFullName = model.ByEmployee.FullName;
                        break;
                    case CommandAction.Reject:
                        updateReferenceTableRecordStatus = "J";
                        task.RejectedBy = model.ByEmpId;
                        task.RejectedWhen = DateTime.Now;
                        task.RejectedByPosName = model.ByEmployee.Position.PosNo + " - " + model.ByEmployee.Position.Name;
                        task.RejectedByFullName = model.ByEmployee.FullName;
                        break;
                    case CommandAction.Forward: // Forward จริงๆ อันนี้ไม่น่าจะมีในตอนEvent Complete 
                    case CommandAction.TakeBack: // คนที่ส่งมาในขั้นตอนที่แล้ว ดึงกลับขั้นตอน (ก่อนผู้อนุมัติขั้นตอนปัจจุบันจะทำคำสั่งไปเป็น Step ต่อไป) #รอ Implement ยังไม่แน่ใจว่าจะทำได้ยังไง
                    case CommandAction.SendBack: // ส่งย้อนกลับไปยังขั้นตอนใดๆ ก็ได้ที่อยู่ก่อนหน้า #รอ Implement ยังไม่แน่ใจว่าจะทำได้ยังไง 
                    case CommandAction.Pending: // Pending ผู้บังคับบัญชาสั่งพักคำขอไว้ #ต้องทำอะไรบ้าง ยังไม่แน่ใจ
                    case CommandAction.Information: //กรณี R/Read-Only / แจ้งให้ทราบอย่างเดียว ไม่มี Action (ยังไม่แน่ใจว่าจะมาตรง Complete ได้อย่างไร)
                    case CommandAction.Lock: // Lock By System อาจเกิด Error เลยทำให้โปรแกรมสั่ง Command Lock (ต้องไปเช็คดูอีกที)
                    default:
                        updateReferenceTableRecordStatus = null;
                        break;
                }

                var history = db.TaskHistories.Find(model.TaskHistoryId);
                model.TaskHistory = new GERPContext(false).TaskHistories.Find(model.TaskHistoryId);

                if (history != null)
                {
                    if (!string.IsNullOrWhiteSpace(model.Remarks) && !string.IsNullOrWhiteSpace(history.Remark))
                    {
                        history.Remark = model.Remarks + ", " + history.Remark;
                    }
                    if (string.IsNullOrWhiteSpace(model.Remarks) && !string.IsNullOrWhiteSpace(history.Remark))
                    {
                        history.Remark = history.Remark;
                    }
                    if (!string.IsNullOrWhiteSpace(model.Remarks) && string.IsNullOrWhiteSpace(history.Remark))
                    {
                        history.Remark = model.Remarks;
                    }
                }
                string strSQL = "";
                task.ApproveProcess = db.ApproveProcesses.FirstOrDefault(o => o.AppPrcId == task.AppPrcId);

                switch (task.ApproveProcess.AppPrcGrpId)
                {
                    case 1:
                        #region AppPrcGrpId1
                        if (!string.IsNullOrWhiteSpace(updateReferenceTableRecordStatus))
                        {
                            db.PsAbsences.FirstOrDefault(o => o.AbsenceId == task.RefId).RecordStatus = updateReferenceTableRecordStatus;
                        }
                        #endregion
                        break;
                    case 6:
                        #region AppPrcGrpId6
                        if (!string.IsNullOrWhiteSpace(updateReferenceTableRecordStatus))
                        {
                            var prePurchareRequest = db.PrePurchaseRequests.FirstOrDefault(o => o.PprId == task.RefId);
                            prePurchareRequest.RecordStatus = updateReferenceTableRecordStatus;
                            prePurchareRequest.Reason = model.Remarks + " - " + prePurchareRequest.Reason;
                        }

                        List<PrePurchaseRequestItem> ppriModels = db.PrePurchaseRequestItems.Where(o => o.PprId == task.RefId).ToList();

                        foreach (var ppriModel in ppriModels)
                        {
                            ProjectOperationReport reportModel = new ProjectOperationReport();

                            reportModel.ProjOperExpId = ppriModel.ProjOperExpId;
                            reportModel.PprItemId = ppriModel.PprItemId;
                            reportModel.UsedAmount = ppriModel.Amount ?? 0;
                            reportModel.CalAmount = -ppriModel.Amount ?? 0;
                            reportModel.RptType = 4;
                            reportModel.RecordStatus = RecordStatus.UsedStatus;
                            reportModel.CreatedBy = ppriModel.CreatedBy;
                            reportModel.CreatedWhen = DateTime.Now;

                            db.ProjectOperationReports.Add(reportModel);
                        }
                        #endregion
                        break;
                    case 7:
                        #region AppPrcGrpId7
                        if (!string.IsNullOrWhiteSpace(updateReferenceTableRecordStatus)) db.RequestParcels.FirstOrDefault(o => o.ReqParcelId == task.RefId).RecordStatus = updateReferenceTableRecordStatus;
                        #endregion
                        break;
                    case 8:
                    case 9:
                        #region AppPrcGrpId8
                        if (!string.IsNullOrWhiteSpace(updateReferenceTableRecordStatus)) db.PurchaseRequests.FirstOrDefault(o => o.PrId == task.RefId).RecordStatus = updateReferenceTableRecordStatus;

                        List<PurchaseRequestItem> priModels = db.PurchaseRequestItems.Where(o => o.PrId == task.RefId).ToList();

                        foreach (var priModel in priModels ?? new List<PurchaseRequestItem>())
                        {
                            List<PrePurchaseRequestItem> ppriModels1 = db.PrePurchaseRequestItems.Where(o => o.PrItemId == priModel.PrItemId).ToList();

                            foreach (var ppriModel in ppriModels1 ?? new List<PrePurchaseRequestItem>())
                            {
                                ProjectOperationReport reportModel = db.ProjectOperationReports.FirstOrDefault(o => o.PprItemId == ppriModel.PprItemId);

                                if (reportModel != null)
                                {
                                    reportModel.ProjOperExpId = ppriModel.ProjOperExpId;
                                    reportModel.PprItemId = ppriModel.PprItemId;
                                    reportModel.PrItemId = ppriModel.PrItemId;
                                    reportModel.UsedAmount = ((priModel.Amount ?? 0) / (priModel.Qty ?? 1)) * (ppriModel.Qty ?? 1);
                                    reportModel.CalAmount = -(((priModel.Amount ?? 0) / (priModel.Qty ?? 1)) * (ppriModel.Qty ?? 1));
                                    reportModel.RptType = 4;
                                    reportModel.RecordStatus = RecordStatus.UsedStatus;
                                    reportModel.UpdatedBy = priModel.CreatedBy;
                                    reportModel.UpdatedWhen = DateTime.Now;

                                    db.Entry(reportModel).State = System.Data.Entity.EntityState.Modified;
                                }
                                else
                                {
                                    reportModel = new ProjectOperationReport();

                                    reportModel.ProjOperExpId = ppriModel.ProjOperExpId;
                                    reportModel.PprItemId = ppriModel.PprItemId;
                                    reportModel.PrItemId = ppriModel.PrItemId;
                                    reportModel.UsedAmount = ((priModel.Amount ?? 0) / (priModel.Qty ?? 1)) * (ppriModel.Qty ?? 1);
                                    reportModel.CalAmount = -(((priModel.Amount ?? 0) / (priModel.Qty ?? 1)) * (ppriModel.Qty ?? 1));
                                    reportModel.RptType = 4;
                                    reportModel.RecordStatus = RecordStatus.UsedStatus;
                                    reportModel.CreatedBy = priModel.CreatedBy;
                                    reportModel.CreatedWhen = DateTime.Now;

                                    db.ProjectOperationReports.Add(reportModel);
                                }
                            }
                        }
                        #endregion
                        break;
                    case 10:
                    case 11:
                    case 13:
                        #region AppPrcGrpId10
                        if (!string.IsNullOrWhiteSpace(updateReferenceTableRecordStatus)) db.PurchaseOrders.FirstOrDefault(o => o.PoId == task.RefId).RecordStatus = updateReferenceTableRecordStatus;

                        List<PurchaseOrderItem> poiModels = db.PurchaseOrderItems.Include("BidderQuotation").Where(o => o.PoId == task.RefId).ToList();

                        foreach (var poiModel in poiModels ?? new List<PurchaseOrderItem>())
                        {
                            List<PrePurchaseRequestItem> ppriModels2 = db.PrePurchaseRequestItems.Where(o => o.PrItemId == poiModel.BidderQuotation.PrItemId).ToList();

                            foreach (var ppriModel in ppriModels2 ?? new List<PrePurchaseRequestItem>())
                            {
                                ProjectOperationReport reportModel = db.ProjectOperationReports.FirstOrDefault(o => o.PprItemId == ppriModel.PprItemId);

                                if (reportModel != null)
                                {
                                    reportModel.ProjOperExpId = ppriModel.ProjOperExpId;
                                    reportModel.PprItemId = ppriModel.PprItemId;
                                    reportModel.PrItemId = ppriModel.PrItemId;
                                    //reportModel.PoInstallItemId = poiModel.PoItemId;
                                    reportModel.UsedAmount = ((poiModel.BidderQuotation.NetAmount ?? 0) / (poiModel.BidderQuotation.Qty ?? 1)) * (ppriModel.Qty ?? 1);
                                    reportModel.CalAmount = -(((poiModel.BidderQuotation.NetAmount ?? 0) / (poiModel.BidderQuotation.Qty ?? 1)) * (ppriModel.Qty ?? 1));
                                    reportModel.RptType = 4;
                                    reportModel.RecordStatus = RecordStatus.UsedStatus;
                                    reportModel.UpdatedBy = poiModel.CreatedBy;
                                    reportModel.UpdatedWhen = DateTime.Now;

                                    db.Entry(reportModel).State = System.Data.Entity.EntityState.Modified;
                                }
                                else
                                {
                                    reportModel = new ProjectOperationReport();

                                    reportModel.ProjOperExpId = ppriModel.ProjOperExpId;
                                    reportModel.PprItemId = ppriModel.PprItemId;
                                    reportModel.PrItemId = ppriModel.PrItemId;
                                    //reportModel.PoInstallItemId = poiModel.PoItemId;
                                    reportModel.UsedAmount = ((poiModel.BidderQuotation.NetAmount ?? 0) / (poiModel.BidderQuotation.Qty ?? 1)) * (ppriModel.Qty ?? 1);
                                    reportModel.CalAmount = -(((poiModel.BidderQuotation.NetAmount ?? 0) / (poiModel.BidderQuotation.Qty ?? 1)) * (ppriModel.Qty ?? 1));
                                    reportModel.RptType = 4;
                                    reportModel.RecordStatus = RecordStatus.UsedStatus;
                                    reportModel.UpdatedBy = poiModel.CreatedBy;
                                    reportModel.UpdatedWhen = DateTime.Now;

                                    db.ProjectOperationReports.Add(reportModel);
                                }
                            }
                        }
                        #endregion
                        break;
                    case 12:
                        #region AppPrcGrpId12
                        if (!string.IsNullOrWhiteSpace(updateReferenceTableRecordStatus))
                            db.Bids.FirstOrDefault(o => o.BidId == task.RefId).RecordStatus = updateReferenceTableRecordStatus;
                        #endregion
                        break;
                    case 14:
                        #region DeliveryNote

                        DeliveryNote model14 = db.DeliveryNotes.Include("PurchaseOrder.Bidder").FirstOrDefault(o => o.DnId == task.RefId);
                        List<DeliveryNoteItem> modelItems14 = db.DeliveryNoteItems.Include("PoInstallItem.BidderQuotation.PurchaseRequestItem")
                            .Where(o => o.DnId == task.RefId).ToList();
                        double Penalty = db.DeliveryNotePenalties
                            .Where(o => o.DnId == task.RefId).Sum(m => m.PenaltyFinalAmount) ?? 0;

                        model14.RecordStatus = updateReferenceTableRecordStatus;
                        int oldProjOperId = 0;
                        int nMoneyReqSeq = 0;

                        foreach (var modelItem14 in modelItems14)
                        {
                            List<PrePurchaseRequestItem> modelPreItems = db.PrePurchaseRequestItems
                                .Include("ProjectOperationExpense.ProjectExpense.MasBudgetExpense")
                                .Include("ProjectOperationExpense.ProjectOperation")
                                .Where(m => m.PrItemId == modelItem14.PoInstallItem.BidderQuotation.PurchaseRequestItem.PrItemId).OrderBy(m => m.ProjectOperationExpense.ProjOperId).ToList();

                            for (int i = 0; i < modelPreItems.Count; i++)
                            {
                                var modelPreItem = modelPreItems[i];

                                if (oldProjOperId != modelPreItem.ProjectOperationExpense.ProjOperId)
                                {
                                    strSQL = "insert into fim_money_requests"
                                             + " (bgm_source_seq, bgm_year, work_area_seq"
                                             + ", bgm_exp_grp_seq, event_seq, money_req_date, use_advance"
                                             + ", advance_bgm_source_seq, ref_no, money_req_name"
                                             + ", bgm_proj_oper_seq, emp_seq, job_site_seq"
                                             + ", contact_seq, icm_dn_seq, allow"
                                             + ", record_status, created_by, created_when)"
                                             + " values"
                                             + " (@bgm_source_seq, @bgm_year, @work_area_seq"
                                             + ", @bgm_exp_grp_seq, @event_seq, @money_req_date, @use_advance"
                                             + ", @advance_bgm_source_seq, @ref_no, @money_req_name"
                                             + ", @bgm_proj_oper_seq, @emp_seq, @job_site_seq"
                                             + ", @contact_seq, @icm_dn_seq, @allow"
                                             + ", @record_status, @created_by, @created_when);"
                                             + " select scope_identity();";

                                    strSQL = strSQL.Replace("@bgm_source_seq", modelPreItem.ProjectOperationExpense.ProjectExpense.BgSourceId.HasValue ? modelPreItem.ProjectOperationExpense.ProjectExpense.BgSourceId.ToString() : "null")
                                        .Replace("@bgm_year", modelPreItem.ProjectOperationExpense.ProjectOperation.BgYear.HasValue ? modelPreItem.ProjectOperationExpense.ProjectOperation.BgYear.ToString() : "null")
                                        .Replace("@work_area_seq", "1")
                                        .Replace("@bgm_exp_grp_seq", modelPreItem.ProjectOperationExpense.ProjectExpense.MasBudgetExpense.BgExpGrpId.HasValue ? modelPreItem.ProjectOperationExpense.ProjectExpense.MasBudgetExpense.BgExpGrpId.ToString() : "null")
                                        .Replace("@event_seq", "11")
                                        .Replace("@money_req_date", "'" + (DateTime.Today.Year) + "/" + DateTime.Today.Month + "/" + DateTime.Today.Day + "'")
                                        .Replace("@use_advance", "'0'")
                                        .Replace("@advance_bgm_source_seq", "null")
                                        .Replace("@ref_no", "'" + CodeGenerate.GetNextCode(CodeGenerate.TransectionType.MoneyRequest, (modelPreItem.ProjectOperationExpense.ProjectExpense.BgSourceId ?? 1) == 1 ? "1" : "2") + "'")
                                        .Replace("@money_req_name", "'รายการเบิกเงินจากการตรวจรับเลขที่ " + model14.Code + "'")
                                        .Replace("@bgm_proj_oper_seq", modelPreItem.ProjectOperationExpense.ProjOperId.HasValue ? modelPreItem.ProjectOperationExpense.ProjOperId.ToString() : "null")
                                        .Replace("@emp_seq", model14.DeliveryEmpId.HasValue ? model14.DeliveryEmpId.ToString() : model14.CreatedBy.HasValue ? model14.CreatedBy.ToString() : model14.UpdatedBy.HasValue ? model14.UpdatedBy.ToString() : model.ByEmpId.ToString())
                                        .Replace("@job_site_seq", modelPreItem.ProjectOperationExpense.ProjectOperation.JobSiteId.HasValue ? modelPreItem.ProjectOperationExpense.ProjectOperation.JobSiteId.ToString() : "null")
                                        .Replace("@contact_seq", model14.PurchaseOrder.Bidder.ConId.ToString().Length > 0 ? model14.PurchaseOrder.Bidder.ConId.ToString() : "null")
                                        .Replace("@icm_dn_seq", model14.DnId.ToString().Length > 0 ? model14.DnId.ToString() : "null")
                                        .Replace("@allow", "'1'")
                                        .Replace("@record_status", "'W'")
                                        .Replace("@created_by", model14.DeliveryEmpId.HasValue ? model14.DeliveryEmpId.ToString() : model.ByEmpId.ToString())
                                        .Replace("@created_when", "'" + (DateTime.Today.Year) + "/" + DateTime.Today.Month + "/" + DateTime.Today.Day + "'");

                                    DBManager dbManager = new DBManager(DataProvider.SqlServer, System.Configuration.ConfigurationManager.ConnectionStrings["GERPContext"].ConnectionString);
                                    dbManager.Open();
                                    nMoneyReqSeq = Convert.ToInt32(dbManager.ExecuteDataTable(CommandType.Text, strSQL).Rows[0][0]);
                                    //var nMoneyReqSeq = db.Database.ExecuteSqlCommand(strSQL);
                                    dbManager.Close();

                                    oldProjOperId = modelPreItem.ProjectOperationExpense.ProjOperId ?? 0;

                                    strSQL = "insert into fim_money_request_dtls"
                                         + " (money_req_seq, money_req_dtls_name, bgm_proj_oper_exp_seq"
                                         + ", job_site_seq, percent_vat, percent_tax, amount"
                                         + ", amount_vat, amount_tax"
                                         + ", record_status, created_by, created_when)"
                                         + " values"
                                         + " (@money_req_seq, @money_req_dtls_name, @bgm_proj_oper_exp_seq"
                                         + ", @job_site_seq, @percent_vat, @percent_tax, @amount"
                                         + ", @amount_vat, @amount_tax"
                                         + ", @record_status, @created_by, @created_when)";

                                    strSQL = strSQL.Replace("@money_req_seq", nMoneyReqSeq.ToString())
                                        .Replace("@money_req_dtls_name", "'" + (modelItem14.ResItemId.HasValue ? modelItem14.MasResourceItem.Name
                                                                                    : (modelItem14.Descr != null ? modelItem14.Descr : "")) + "'")
                                        .Replace("@bgm_proj_oper_exp_seq", modelPreItem.ProjectOperationExpense.ProjOperExpId.ToString())
                                        .Replace("@job_site_seq", modelPreItem.ProjectOperationExpense.ProjectOperation.JobSiteId.HasValue ? modelPreItem.ProjectOperationExpense.ProjectOperation.JobSiteId.ToString() : "null")
                                        .Replace("@percent_vat", "null")
                                        .Replace("@percent_tax", "null")
                                        .Replace("@amount_vat", modelItem14.VatAmount.HasValue ? (((modelItem14.VatAmount ?? 0) * (modelPreItem.Qty ?? 1) / (modelItem14.PoInstallItem.BidderQuotation.Qty ?? 1)) - (Penalty - ((Penalty * 100) / 107))).ToString() : "null")
                                        .Replace("@amount_tax", "null")
                                        .Replace("@amount", modelItem14.NetAmount.HasValue ? (((modelItem14.NetAmount ?? 0) * (modelPreItem.Qty ?? 1) / (modelItem14.PoInstallItem.BidderQuotation.Qty ?? 1)) - Penalty).ToString() : "null")
                                        .Replace("@record_status", "'W'")
                                        .Replace("@created_by", model14.DeliveryEmpId.HasValue ? model14.DeliveryEmpId.ToString() : model.ByEmpId.ToString())
                                        .Replace("@created_when", "getdate()");

                                    db.Database.ExecuteSqlCommand(strSQL);
                                }
                                else
                                {
                                    strSQL = "insert into fim_money_request_dtls"
                                         + " (money_req_seq, money_req_dtls_name, bgm_proj_oper_exp_seq"
                                         + ", job_site_seq, percent_vat, percent_tax, amount"
                                         + ", amount_vat, amount_tax"
                                         + ", record_status, created_by, created_when)"
                                         + " values"
                                         + " (@money_req_seq, @money_req_dtls_name, @bgm_proj_oper_exp_seq"
                                         + ", @job_site_seq, @percent_vat, @percent_tax, @amount"
                                         + ", @amount_vat, @amount_tax"
                                         + ", @record_status, @created_by, @created_when)";

                                    strSQL = strSQL.Replace("@money_req_seq", nMoneyReqSeq.ToString())
                                        .Replace("@money_req_dtls_name", "'" + (modelItem14.ResItemId.HasValue ? modelItem14.MasResourceItem.Name
                                                                                    : (modelItem14.Descr != null ? modelItem14.Descr : "")) + "'")
                                        .Replace("@bgm_proj_oper_exp_seq", modelPreItem.ProjectOperationExpense.ProjOperExpId.ToString())
                                        .Replace("@job_site_seq", modelPreItem.ProjectOperationExpense.ProjectOperation.JobSiteId.HasValue ? modelPreItem.ProjectOperationExpense.ProjectOperation.JobSiteId.ToString() : "null")
                                        .Replace("@percent_vat", "null")
                                        .Replace("@percent_tax", "null")
                                        .Replace("@amount_vat", modelItem14.VatAmount.HasValue ? ((modelItem14.VatAmount ?? 0) * (modelPreItem.Qty ?? 1) / (modelItem14.PoInstallItem.BidderQuotation.Qty ?? 1)).ToString() : "null")
                                        .Replace("@amount_tax", "null")
                                        .Replace("@amount", modelItem14.NetAmount.HasValue ? ((modelItem14.NetAmount ?? 0) * (modelPreItem.Qty ?? 1) / (modelItem14.PoInstallItem.BidderQuotation.Qty ?? 1)).ToString() : "null")
                                        .Replace("@record_status", "'W'")
                                        .Replace("@created_by", model14.DeliveryEmpId.HasValue ? model14.DeliveryEmpId.ToString() : model.ByEmpId.ToString())
                                        .Replace("@created_when", "getdate()");

                                    db.Database.ExecuteSqlCommand(strSQL);
                                }
                            }
                        }

                        #endregion
                        break;
                    case 15:
                        #region AppPrcGrpId15
                        if (!string.IsNullOrWhiteSpace(updateReferenceTableRecordStatus)) db.MpPositionWorkOnDuties.FirstOrDefault(o => o.WodId == task.RefId).RecordStatus = updateReferenceTableRecordStatus;

                        //error รึเปล่า
                        /*
                        if (!string.IsNullOrWhiteSpace(updateReferenceTableRecordStatus))
                        {
                            var command = db.PsCommands.FirstOrDefault(o => o.CmdId == task.RefId);
                            command.RecordStatus = updateReferenceTableRecordStatus;
                            command.CmdStatus = "A";
                            if (model.CommandAction == CommandAction.Approve)
                            {
                                command.ApproverId = model.ByEmpId;
                                command.ApprovedPositions = Convert.ToString(db.Employees.Find(model.ByEmpId).PosId);
                                command.ApprovedName = db.Employees.Find(model.ByEmpId).FullName;
                                command.ApprovedDate = DateTime.Now;
                            }
                        }
                        */
                        #endregion
                        break;

                }

                Task thisApprovalTask = db.Tasks.Find(task.TaskId);
                thisApprovalTask.Completed = true;
                thisApprovalTask.CompletedBy = model.ByEmpId;
                thisApprovalTask.CompletedWhen = DateTime.Now;
                thisApprovalTask.CompletedByPosName = model.ByEmployee.Position.PosNo + " - " + model.ByEmployee.Position.Name;
                thisApprovalTask.CompletedByFullName = model.ByEmployee.FullName;

                task.Completed = true;
                task.CurrentTaskHistoryId = null;
                task.LastestActionCode = model.CommandAction.ToString();
                task.LastestActionBy = model.ByEmpId;
                task.LastestActionByPosName = "" + model.ByEmployee.Position.PosNo + " - " + model.ByEmployee.Position.Name;
                task.LastestActionByFullName = model.ByEmployee.FullName;
                db.SaveChanges();
            }

            // Patch for send back to WEB
            using (var db = new GERPContext(false))
            {
                var history = db.TaskHistories.Find(model.TaskHistoryId);
                model.TaskHistory = history;
            }
        }

        public void OnWorkflowFailed(int? taskId, CreateTaskVM modelCreate, CommandActionVM modelEscalate, string errorReasons)
        {
            try
            {

                using (GERPContext db = new GERPContext(true))
                {
                    var task = db.Tasks.Find(taskId);
                    task.Remark = errorReasons + "\r\n" + errorReasons;
                    task.RecordStatus = "Failed";
                    task.UpdatedBy = modelCreate == null ? modelEscalate.ByEmpId : modelCreate.CreateByEmpId;
                    task.UpdatedWhen = DateTime.Now;
                }
            }
            catch (Exception ex)
            {
                GerpLog.Error(ex.Message, ex);
            }
        }

        public void OnWorkflowSendBack(int taskId)
        {
            try
            {

                using (GERPContext db = new GERPContext(true))
                {
                    var task = db.Tasks.Find(taskId);
                    // 1. Update ConfirmStatus;
                    var ApproveProcess = db.ApproveProcesses.Find(task.AppPrcId);
                    if (ApproveProcess.AppPrcGrpId == 1) // ขอลา ทั้งกลุ่ม Process ใช้วิธีนี้หมด
                    {
                        db.PsAbsences.FirstOrDefault(o => o.AbsenceId == task.RefId).RecordStatus = "R";
                    }
                    else if (ApproveProcess.AppPrcGrpId == 6) // คำขออนุมัติแจ้งความประสงค์
                    {
                        db.PrePurchaseRequests.FirstOrDefault(o => o.PprId == task.RefId).RecordStatus = "R";
                    }
                    else if (ApproveProcess.AppPrcGrpId == 7) // ขอเบิก
                    {
                        db.RequestParcels.FirstOrDefault(o => o.ReqParcelId == task.RefId).RecordStatus = "R";
                    }
                    else if (ApproveProcess.AppPrcGrpId == 8 // ขออนุมัติขอซื้อขอจ้างเกิน๑๐๐๐๐๐บาท
                             || ApproveProcess.AppPrcGrpId == 9) // ขออนุมัติขอซื้อขอจ้างไม่เกิน๑๐๐๐๐๐บาท
                    {
                        db.PurchaseRequests.FirstOrDefault(o => o.PrId == task.RefId).RecordStatus = "R";
                    }
                    else if (ApproveProcess.AppPrcGrpId == 10 // ขออนุมัติใบสั่งเกิน๑๐๐๐๐๐บาท
                             || ApproveProcess.AppPrcGrpId == 11 // ขออนุมัติใบสั่งไม่เกิน๑๐๐๐๐๐บาท
                             || ApproveProcess.AppPrcGrpId == 13) // ขออนุมัติงานสัญญา
                    {
                        db.PurchaseOrders.FirstOrDefault(o => o.PoId == task.RefId).RecordStatus = "R";
                    }
                    else if (ApproveProcess.AppPrcGrpId == 12) // ขออนุมัติประกาศ
                    {
                        db.Bids.FirstOrDefault(o => o.BidId == task.RefId).RecordStatus = "R";
                    }
                    else if (ApproveProcess.AppPrcGrpId == 14) // ตรวจรับ
                    {
                        db.DeliveryNotes.FirstOrDefault(o => o.DnId == task.RefId).RecordStatus = "R";
                    }
                    else if (ApproveProcess.AppPrcGrpId == 2) // คำสั่ง
                    {
                        db.PsCommands.FirstOrDefault(o => o.CmdId == task.RefId).RecordStatus = "R";
                    }
                    else if (ApproveProcess.AppPrcGrpId == 15) // คำสั่ง
                    {
                        db.MpPositionWorkOnDuties.FirstOrDefault(o => o.WodId == task.RefId).RecordStatus = "R";
                    }
                    db.SaveChanges();
                }
            }
            catch (Exception ex)
            {
                GerpLog.Error(ex.Message, ex);
            }
        }

        public void OnWorkflowSendBackReturn(int taskId)
        {
            try
            {

                using (GERPContext db = new GERPContext(true))
                {
                    var task = db.Tasks.Find(taskId);

                    // 1. Update ConfirmStatus;
                    var ApproveProcess = db.ApproveProcesses.Find(task.AppPrcId);
                    if (ApproveProcess.AppPrcGrpId == 1) // ขอลา ทั้งกลุ่ม Process ใช้วิธีนี้หมด
                    {
                        db.PsAbsences.FirstOrDefault(o => o.AbsenceId == task.RefId).RecordStatus = task.ConfirmAs;
                    }
                    else if (ApproveProcess.AppPrcGrpId == 6) // คำขออนุมัติแจ้งความประสงค์
                    {
                        db.PrePurchaseRequests.FirstOrDefault(o => o.PprId == task.RefId).RecordStatus = task.ConfirmAs;
                    }
                    else if (ApproveProcess.AppPrcGrpId == 7) // ขอเบิก
                    {
                        db.RequestParcels.FirstOrDefault(o => o.ReqParcelId == task.RefId).RecordStatus = task.ConfirmAs;
                    }
                    else if (ApproveProcess.AppPrcGrpId == 8 // ขออนุมัติขอซื้อขอจ้างเกิน๑๐๐๐๐๐บาท
                             || ApproveProcess.AppPrcGrpId == 9) // ขออนุมัติขอซื้อขอจ้างไม่เกิน๑๐๐๐๐๐บาท
                    {
                        db.PurchaseRequests.FirstOrDefault(o => o.PrId == task.RefId).RecordStatus = task.ConfirmAs;
                    }
                    else if (ApproveProcess.AppPrcGrpId == 10 // ขออนุมัติใบสั่งเกิน๑๐๐๐๐๐บาท
                             || ApproveProcess.AppPrcGrpId == 11 // ขออนุมัติใบสั่งไม่เกิน๑๐๐๐๐๐บาท
                             || ApproveProcess.AppPrcGrpId == 13) // ขออนุมัติงานสัญญา
                    {
                        db.PurchaseOrders.FirstOrDefault(o => o.PoId == task.RefId).RecordStatus = task.ConfirmAs;
                    }
                    else if (ApproveProcess.AppPrcGrpId == 12) // ขออนุมัติประกาศ
                    {
                        db.Bids.FirstOrDefault(o => o.BidId == task.RefId).RecordStatus = task.ConfirmAs;
                    }
                    else if (ApproveProcess.AppPrcGrpId == 14) // ตรวจรับ
                    {
                        db.DeliveryNotes.FirstOrDefault(o => o.DnId == task.RefId).RecordStatus = task.ConfirmAs;
                    }
                    else if (ApproveProcess.AppPrcGrpId == 2) // คำสั่ง
                    {
                        db.PsCommands.FirstOrDefault(o => o.CmdId == task.RefId).RecordStatus = task.ConfirmAs;
                    }
                    else if (ApproveProcess.AppPrcGrpId == 15)
                    {
                        db.MpPositionWorkOnDuties.FirstOrDefault(o => o.WodId == task.RefId).RecordStatus = task.ConfirmAs;
                    }
                    db.SaveChanges();
                }
            }
            catch (Exception ex)
            {
                GerpLog.Error(ex.Message, ex);
            }
        }
    }
}